package Dao;

import DBCPUtil.DBCPUtil;
import Dto.CustomerCompany;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class CustomerCompanyDao {
    QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());

    //列表 分页
    public List<CustomerCompany> selectCustomerCompanyList(Integer pageNum) throws SQLException {
        String sql = "select * from customercompany limit ?,?";
        List<CustomerCompany> list = qr.query(sql, new BeanListHandler<>(CustomerCompany.class), (pageNum-1)*10, 10);
        return list;
    }

    //数据条数
    public Long selectCustomerCompanyCount() throws SQLException {
        String sql = "select count(*) from customercompany";
        Long count = qr.query(sql, new ScalarHandler<>());
        return count;
    }

    //客户公司添加
    public void addCustomerCompany(CustomerCompany customerCompany) throws SQLException {
        String sql = "insert into customercompany (name, address, phone, work) values (?, ?, ?, ?)";
        qr.update(sql, customerCompany.getName(), customerCompany.getAddress(), customerCompany.getPhone(), customerCompany.getWork());
    }

    //客户公司预修改
    public CustomerCompany preUpdateCustomerCompany(Integer id) throws SQLException {
        String sql = "select * from customercompany where id = ?";
        CustomerCompany customerCompany = qr.query(sql, new BeanHandler<>(CustomerCompany.class), id);
        return customerCompany;
    }

    //客户公司修改
    public void updateCustomerCompany(CustomerCompany customerCompany) throws SQLException {
        String sql = "update customercompany set name=?, address=?, phone=?, work=? where id=?";
        qr.update(sql, customerCompany.getName(), customerCompany.getAddress(), customerCompany.getPhone(), customerCompany.getWork(), customerCompany.getId());
    }

    //客户公司删除
    public void deleteCustomerCompany(Integer id) throws SQLException {
        String sql = "delete from customercompany where id=?";
        qr.update(sql, id);
    }

    //对公司名称模糊查询
    public List<CustomerCompany> selectByName(String name) throws SQLException {
        String sql = "select * from customercompany where name like ?";
        List<CustomerCompany> list = qr.query(sql, new BeanListHandler<>(CustomerCompany.class), "%" + name + "%");
        return list;
    }

    //对公司业务模糊查询
    public List<CustomerCompany> selectByWork(String work) throws SQLException {
        String sql = "select * from customercompany where work like ?";
        List<CustomerCompany> list = qr.query(sql, new BeanListHandler<>(CustomerCompany.class), "%" + work + "%");
        return list;
    }

    //查询所有公司
    public List<CustomerCompany> selectAll() throws SQLException {
        String sql = "select * from customercompany";
        List<CustomerCompany> list = qr.query(sql, new BeanListHandler<>(CustomerCompany.class));
        return list;
    }
    public List<String> selectFirm() throws SQLException {
        String sql = "select name from customercompany";
        List<String> list = qr.query(sql,new ColumnListHandler<>());
        return list;
    }

}